using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections.Generic;
using Acms.SqlDal;

/// <summary>
/// Summary description for School
/// </summary>
public class School : BaseItem
{
    public enum SchoolTypes : short
    {
        None = 0,
        Primary = 1,
        Middle = 2,
        Secondary = 3
    }

    public School() : base(){}
    private School(object concurencyId) : base(concurencyId) { }

    private int id;
    public int Id
    {
        get { return id; }
    }

    private string schoolName;
    public string SchoolName
    {
        get { return schoolName; }
        set { schoolName = value; }
    }

    private short schoolType;
    public SchoolTypes SchoolType
    {
        get { return (SchoolTypes)schoolType; }
        set { schoolType = (short)value; }
    }

    private string addressLine1;
    public string AddressLine1
    {
        get { return addressLine1; }
        set { addressLine1 = value; }
    }

    private string addressLine2;
    public string AddressLine2
    {
        get { return addressLine2; }
        set { addressLine2 = value; }
    }

    private string addressLine3;
    public string AddressLine3
    {
        get { return addressLine3; }
        set { addressLine3 = value; }
    }

    private string town;
    public string Town
    {
        get { return town; }
        set { town = value; }
    }
    
    private string county;
    public string County
    {
        get { return county; }
        set { county = value; }
    }

    private string postCode;
    public string PostCode
    {
        get { return postCode; }
        set { postCode = value; }
    }

    private string schoolTel;
    public string SchoolTel
    {
        get { return schoolTel; }
        set { schoolTel = value; }
    }

    private string contactFirstName;
    public string ContactFirstName
    {
        get { return contactFirstName; }
        set { contactFirstName = value; }
    }

    private string contactLastName;
    public string ContactLastName
    {
        get { return contactLastName; }
        set { contactLastName = value; }
    }
    
    private string contactEmailAddress;
    public string ContactEmailAddress
    {
        get { return contactEmailAddress; }
        set { contactEmailAddress = value; }
    }

    private string contactTel;
    public string ContactTel
    {
        get { return contactTel; }
        set { contactTel = value; }
    }

    private string secondaryContactName;
    public string SecondaryContactName
    {
        get { return secondaryContactName; }
        set { secondaryContactName = value; }
    }

    private string password;
    public string Password
    {
        get { return password; }
        set { password = value; }
    }

    private DateTime created;
    public DateTime Created
    {
        get { return created; }
    }

    private DateTime lastUpdated;
    public DateTime LastUpdated
    {
        get { return lastUpdated; }
    }

    public string ContactDisplayName
    {
        get
        {
            string tmp = string.Concat(ContactFirstName, " ", ContactLastName);
            tmp = tmp.Trim();
            while (tmp.Contains("  "))
            {
                tmp = tmp.Replace("  ", " ");
            }
            return tmp;
        }
    }

    public string SchoolDisplayName
    {
        get
        {
            string tmp = string.Concat(SchoolName, ", ", Town);
            tmp = tmp.Trim();
            while (tmp.Contains(" ,"))
            {
                tmp = tmp.Replace(" ,", ",");
            }
            while (tmp.Contains("  "))
            {
                tmp = tmp.Replace("  ", " ");
            }
            return tmp;
        }
    }

    public void Save()
    {
        base.SaveChanges();
    }

    protected override void Validate(List<ValidationExceptionItem> items)
    {
        //check required fields
        if (string.IsNullOrEmpty(schoolName))
        {
            items.Add(new ValidationExceptionItem("School Name must be entered."));
        }
        else if (SchoolName.EndsWith(" School"))
        {
            SchoolName = SchoolName.Substring(0, SchoolName.Length - 7);
            SchoolName = SchoolName.Trim();
        }

        if (schoolType == 0)
        {
            items.Add(new ValidationExceptionItem("School Type must be entered."));
        }

        if (string.IsNullOrEmpty(addressLine1))
        {
            items.Add(new ValidationExceptionItem("School Address must be entered."));
        }

        if (string.IsNullOrEmpty(town))
        {
            items.Add(new ValidationExceptionItem("School Town must be entered."));
        }

        if (string.IsNullOrEmpty(postCode))
        {
            items.Add(new ValidationExceptionItem("School Post Code must be entered."));
        }
        else if(!ValidatePostCode(postCode))
        {
            items.Add(new ValidationExceptionItem("School Post Code is not valid."));
        }

        if (string.IsNullOrEmpty(schoolTel))
        {
            items.Add(new ValidationExceptionItem("School Telephone Number must be entered."));
        }

        if (string.IsNullOrEmpty(contactFirstName) || string.IsNullOrEmpty(contactLastName))
        {
            items.Add(new ValidationExceptionItem("Contact Name must be entered."));
        }

        if (string.IsNullOrEmpty(contactEmailAddress))
        {
            items.Add(new ValidationExceptionItem("Contact Email must be entered."));
        }
        else if(!ValidateEmail(contactEmailAddress))
        {
            items.Add(new ValidationExceptionItem("Contact Email must be a valid email address."));
        }

        if (string.IsNullOrEmpty(password))
        {
            items.Add(new ValidationExceptionItem("Password must be entered."));
        }
        else if (!ValidatePassword(password))
        {
            items.Add(new ValidationExceptionItem("Password must be between 6 and 14 characters long and can only conatin alphanumeric characters."));
        }

        //check that this email address is unique for the school
        School tmp = School.GetByContactEmail(contactEmailAddress);
        if(tmp != null && tmp.Id != this.Id)
        {
            items.Add(new ValidationExceptionItem("This email address is already registered, please use a different one."));
        }
        
        //check that the school name and town is unique
        tmp = School.GetBySchoolNameAndTown(schoolName, town);
        if (tmp != null && tmp.Id != this.Id)
        {
            items.Add(new ValidationExceptionItem("This school has already been entered."));
        }
    }

    public const string EmailValidationExpression = @"^([0-9a-zA-Z]([-.\w]*[0-9a-zA-Z+])*@([0-9a-zA-Z][-\w]*[0-9a-zA-Z]\.)+[a-zA-Z]{2,9})$";
    private bool ValidateEmail(string emailAddress)
    {
        System.Text.RegularExpressions.Regex regEx = new System.Text.RegularExpressions.Regex(EmailValidationExpression);
        return regEx.IsMatch(emailAddress);
    }

    public const string PostCodeValidationExpression = @"^([Gg][Ii][Rr] 0[Aa]{2})|((([A-Za-z][0-9]{1,2})|(([A-Za-z][A-Ha-hJ-Yj-y][0-9]{1,2})|(([A-Za-z][0-9][A-Za-z])|([A-Za-z][A-Ha-hJ-Yj-y][0-9]?[A-Za-z])))) {0,1}[0-9][A-Za-z]{2})$";
    private bool ValidatePostCode(string postCode)
    {
        System.Text.RegularExpressions.Regex regEx = new System.Text.RegularExpressions.Regex(PostCodeValidationExpression);
        return regEx.IsMatch(postCode);
    }

    public const string PasswordValidationExpression = @"^[A-Za-z0-9]{6,14}";
    private bool ValidatePassword(string password)
    {
        System.Text.RegularExpressions.Regex regEx = new System.Text.RegularExpressions.Regex(PasswordValidationExpression);
        return regEx.IsMatch(password);
    }
    
    
    protected override object Insert()
    {
        lastUpdated = created = DateTime.Now;

        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = @"
            insert into [ccc].[Schools]
                ([SchoolName], [SchoolType], [Address1], [Address2], [Address3], [Town], [PostCode], [County], [SchoolTel], [ContactFirstName], [ContactLastName], [ContactEmail], [ContactTel], [SecondaryContactName], [Password], [Created], [LastUpdated])
            values
                (@SchoolName, @SchoolType, @Address1, @Address2, @Address3, @Town, @PostCode, @County, @SchoolTel, @ContactFirstName, @ContactLastName, @ContactEmail, @ContactTel, @SecondaryContactName, @Password, @Created, @Created)
            set @IdOut = @@identity
            select @ConcurencyIdOut = ConcurencyId from [ccc].[Schools] Where [Id] = @IdOut
        ";
        SqlHelper.AddInputParameter(cmd, "@SchoolName", SchoolName, SqlDbType.VarChar, 100, null, null);
        SqlHelper.AddInputParameter(cmd, "@SchoolType", SchoolType, SqlDbType.SmallInt, null, null, null);
        SqlHelper.AddInputParameter(cmd, "@Address1", AddressLine1, SqlDbType.VarChar, 50, null, null);
        SqlHelper.AddInputParameter(cmd, "@Address2", AddressLine2, SqlDbType.VarChar, 50, null, null);
        SqlHelper.AddInputParameter(cmd, "@Address3", AddressLine3, SqlDbType.VarChar, 50, null, null);
        SqlHelper.AddInputParameter(cmd, "@Town", Town, SqlDbType.VarChar, 50, null, null);
        SqlHelper.AddInputParameter(cmd, "@County", County, SqlDbType.VarChar, 50, null, null);
        SqlHelper.AddInputParameter(cmd, "@PostCode", PostCode, SqlDbType.VarChar, 8, null, null);
        SqlHelper.AddInputParameter(cmd, "@SchoolTel", SchoolTel, SqlDbType.VarChar, 50, null, null);
        SqlHelper.AddInputParameter(cmd, "@ContactFirstName", ContactFirstName, SqlDbType.VarChar, 50, null, null);
        SqlHelper.AddInputParameter(cmd, "@ContactLastName", ContactLastName, SqlDbType.VarChar, 50, null, null);
        SqlHelper.AddInputParameter(cmd, "@ContactEmail", ContactEmailAddress, SqlDbType.VarChar, 50, null, null);
        SqlHelper.AddInputParameter(cmd, "@ContactTel", ContactTel, SqlDbType.VarChar, 50, null, null);
        SqlHelper.AddInputParameter(cmd, "@SecondaryContactName", SecondaryContactName, SqlDbType.VarChar, 50, null, null);
        SqlHelper.AddInputParameter(cmd, "@Password", Password, SqlDbType.VarChar, 50, null, null);
        SqlHelper.AddInputParameter(cmd, "@Created", Created, SqlDbType.DateTime, null, null, null);

        SqlHelper.AddOutputParameter(cmd, "@IdOut", SqlDbType.Int, null, null, null);
        SqlHelper.AddOutputParameter(cmd, "@ConcurencyIdOut", SqlDbType.Timestamp, null, null, null);

        base.ExecuteCommand(cmd);

        id = SqlHelper.GetOutputValue<int>(cmd, "@IdOut");
        return SqlHelper.GetOutputValue<byte[]>(cmd, "@ConcurencyIdOut");
    }

    protected override object Update()
    {
        lastUpdated = DateTime.Now;
        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = @"
            Update [ccc].[Schools]
            Set 
                [SchoolName] = @SchoolName, 
                [SchoolType] = @SchoolType, 
                [AddressLine1] = @AddressLine1, 
                [AddressLine2] = @AddressLine2, 
                [AddressLine3] = @AddressLine3, 
                [Town] = @Town, 
                [County] = @County, 
                [PostCode] = @PostCode, 
                [SchoolTel] = @SchoolTel, 
                [ContactFirstName] = @ContactFirstName, 
                [ContactLastName] = @ContactLastName, 
                [ContactEmailAddress] = @ContactEmailAddress, 
                [ContactTel] = @ContactTel, 
                [SecondaryContactName] = @SecondaryContactName, 
                [Password] = @Password,
                [LastUpdated] = @LastUpdated
            Where Id = @Id and ConcurencyId = @ConcurencyId
            
            if @@rowcount = 1 begin
                select ConcurencyIdOut = ConcurencyId from [ccc].[Schools] where Id = @Id
                return 0
            end
            else if Exists (Select null From [ccc].[Schools] where Id = @Id) begin
                return -100 -- concurency error
            end
            else begin
                return -200 -- row not found
            end
        ";
        SqlHelper.AddInputParameter(cmd, "@Id", Id, SqlDbType.Int, null, null, null);
        SqlHelper.AddInputParameter(cmd, "@SchoolName", SchoolName, SqlDbType.VarChar, 50, null, null);
        SqlHelper.AddInputParameter(cmd, "@SchoolType", SchoolType, SqlDbType.SmallInt, null, null, null);
        SqlHelper.AddInputParameter(cmd, "@AddressLine1", AddressLine1, SqlDbType.VarChar, 50, null, null);
        SqlHelper.AddInputParameter(cmd, "@AddressLine2", AddressLine2, SqlDbType.VarChar, 50, null, null);
        SqlHelper.AddInputParameter(cmd, "@AddressLine3", AddressLine3, SqlDbType.VarChar, 50, null, null);
        SqlHelper.AddInputParameter(cmd, "@Town", Town, SqlDbType.VarChar, 50, null, null);
        SqlHelper.AddInputParameter(cmd, "@County", County, SqlDbType.VarChar, 50, null, null);
        SqlHelper.AddInputParameter(cmd, "@PostCode", PostCode, SqlDbType.VarChar, 50, null, null);
        SqlHelper.AddInputParameter(cmd, "@SchoolTel", SchoolTel, SqlDbType.VarChar, 50, null, null);
        SqlHelper.AddInputParameter(cmd, "@ContactFirstName", ContactFirstName, SqlDbType.VarChar, 50, null, null);
        SqlHelper.AddInputParameter(cmd, "@ContactLastName", ContactLastName, SqlDbType.VarChar, 50, null, null);
        SqlHelper.AddInputParameter(cmd, "@ContactEmailAddress", ContactEmailAddress, SqlDbType.VarChar, 50, null, null);
        SqlHelper.AddInputParameter(cmd, "@ContactTel", ContactTel, SqlDbType.VarChar, 50, null, null);
        SqlHelper.AddInputParameter(cmd, "@SecondaryContactName", SecondaryContactName, SqlDbType.VarChar, 50, null, null);
        SqlHelper.AddInputParameter(cmd, "@Password", Password, SqlDbType.VarChar, 50, null, null);
        SqlHelper.AddInputParameter(cmd, "@LastUpdated", lastUpdated, SqlDbType.DateTime, null, null, null);

        SqlHelper.AddOutputParameter(cmd, "@ConcurencyIdOut", SqlDbType.Timestamp, null, null, null);

        base.ExecuteCommand(cmd);

        return SqlHelper.GetOutputValue<byte[]>(cmd, "ConcurencyIdOut");
    }

    protected override void Delete()
    {
        SqlCommand cmd = new SqlCommand();

        cmd.CommandText = @"
            Delete [ccc].[Schools] where Id = @Id and ConcurencyId = @ConcurencyId
            if @@rowcount = 1 begin
                return 0;
            end
            else if Exists (Select null From [ccc].[Schools] where Id = @Id) begin
                return -100 -- concurency error
            end
            else begin
                return -200 -- row not found
            end
                
        ";

        SqlHelper.AddInputParameter(cmd, "@Id", Id, SqlDbType.Int, null, null, null);
        SqlHelper.AddInputParameter(cmd, "@ConcurencyId", ConcurencyId, SqlDbType.Timestamp, null, null, null);
        base.ExecuteCommand(cmd);
    }

    private static School Populate(SqlDataReader reader)
    {
        if (reader != null)
        {
            School tmp = new School(SqlHelper.GetReaderValue<byte[]>("ConcurencyId", reader));
            tmp.id = SqlHelper.GetReaderValue<int>("Id", reader);
            tmp.schoolName = SqlHelper.GetReaderValue<string>("SchoolName", reader);
            tmp.schoolType = SqlHelper.GetReaderValue<short>("SchoolType", reader);
            tmp.addressLine1 = SqlHelper.GetReaderValue<string>("Address1", reader);
            tmp.addressLine2 = SqlHelper.GetReaderValue<string>("Address2", reader);
            tmp.addressLine3 = SqlHelper.GetReaderValue<string>("Address3", reader);
            tmp.town = SqlHelper.GetReaderValue<string>("Town", reader);
            tmp.county = SqlHelper.GetReaderValue<string>("County", reader);
            tmp.postCode = SqlHelper.GetReaderValue<string>("PostCode", reader);
            tmp.schoolTel = SqlHelper.GetReaderValue<string>("SchoolTel", reader);
            tmp.contactFirstName = SqlHelper.GetReaderValue<string>("ContactFirstName", reader);
            tmp.contactLastName = SqlHelper.GetReaderValue<string>("ContactLastName", reader);
            tmp.contactEmailAddress = SqlHelper.GetReaderValue<string>("ContactEmail", reader);
            tmp.contactTel = SqlHelper.GetReaderValue<string>("ContactTel", reader);
            tmp.secondaryContactName = SqlHelper.GetReaderValue<string>("SecondaryContactName", reader);
            tmp.password = SqlHelper.GetReaderValue<string>("Password", reader);
            tmp.lastUpdated = SqlHelper.GetReaderValue<DateTime>("LastUpdated", reader);
            tmp.created = SqlHelper.GetReaderValue<DateTime>("Created", reader);
            return tmp;
        }
        else
        {
            return null;
        }
    }
    private static School LoadItem(SqlDataReader reader)
    {
        School tmp = null;
        try
        {
            if (reader.Read())
            {
                tmp = Populate(reader);
            }
        }
        finally
        {
            reader.Close();
        }
        return tmp;

    }
    private static List<School> LoadCollection(SqlDataReader reader)
    {
        List<School> tmp = new List<School>();
        try
        {
            while (reader.Read())
            {
                tmp.Add(Populate(reader));
            }
        }
        finally
        {
            reader.Close();
        }

        return tmp;
    }

    public static School GetById(int id)
    {
        School retVal = null;

        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = @"
            select *
            from [ccc].[Schools] 
            where Id = @Id
        ";
        SqlHelper.AddInputParameter(cmd, "@Id", id, SqlDbType.Int, null, null, null);

        SqlConnection connection = new SqlConnection(ConnectionString);

        try
        {
            SqlDataReader reader = SqlHelper.ExecuteDataReaderCommand(cmd, connection, CommandBehavior.CloseConnection);
            retVal = LoadItem(reader);
        }
        finally
        {
            if(connection.State != ConnectionState.Closed)
                connection.Close();
        }
        return retVal;
    }

    public static School GetBySchoolNameAndTown(string schoolName, string town)
    {
        School retVal = null;

        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = @"
            select *
            from [ccc].[Schools] 
            where SchoolName like @SchoolName
                and Town like Town
        ";
        SqlHelper.AddInputParameter(cmd, "@SchoolName", schoolName, SqlDbType.VarChar, 50, null, null);
        SqlHelper.AddInputParameter(cmd, "@Town", town, SqlDbType.VarChar, 50, null, null);

        SqlConnection connection = new SqlConnection(ConnectionString);

        try
        {
            SqlDataReader reader = SqlHelper.ExecuteDataReaderCommand(cmd, connection, CommandBehavior.CloseConnection);
            retVal = LoadItem(reader);
        }
        finally
        {
            if (connection.State != ConnectionState.Closed)
                connection.Close();
        }
        return retVal;
    }

    public static School GetByContactEmail(string contactEmail)
    {
        School retVal = null;

        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = @"
            select *
            from [ccc].[Schools] 
            where ContactEmail like @ContactEmail
        ";
        SqlHelper.AddInputParameter(cmd, "@ContactEmail", contactEmail, SqlDbType.VarChar, 50, null, null);

        SqlConnection connection = new SqlConnection(ConnectionString);
        
        try
        {
            SqlDataReader reader = SqlHelper.ExecuteDataReaderCommand(cmd, connection, CommandBehavior.CloseConnection);
            retVal = LoadItem(reader);
        }
        finally
        {
            if (connection.State != ConnectionState.Closed)
                connection.Close();
        }
        return retVal;
    }

    public static List<School> GetAll()
    {
        List<School> retVal = null;

        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = @"
            select *
            from [ccc].[Schools] 
        ";

        SqlConnection connection = new SqlConnection(ConnectionString);

        try
        {
            SqlDataReader reader = SqlHelper.ExecuteDataReaderCommand(cmd, connection, CommandBehavior.CloseConnection);
            retVal = LoadCollection(reader);
        }
        finally
        {
            if (connection.State != ConnectionState.Closed)
                connection.Close();
        }

        return retVal;
    }
}
